#!/usr/bin/python
# coding:utf-8
import gol
import datetime


def get_user_accnt():
    """
    查询所有用户的所有‘有效’状态的资产配置账户
    """
    sql = """
        SELECT t.accntId,
                t.accntName,
                t.gridFlag,
                s.userId,
                s.userName
        FROM accnts t,users s
        WHERE t.status = 'act'
                AND t.accntUserId = s.userId
        ORDER BY  accntId asc    
    """
    return sql


def get_accnt_asst(accnt_id):
    """
    获得一个账户下的持仓产品及剩余份额，以购买记录左关联卖出记录
    :return: 账户ID，账户持有产品代码，产品剩余份额
    """
    sql = """
        SELECT t2.accntId, t2.prdctCode
            , round(t2.buy_shares - t2.sell_shares, 2) AS left_shares
        FROM (
            SELECT a.*, IFNULL(b.sell_shares, 0) AS sell_shares
            FROM (
                -- 加总购买份额
                SELECT accntId, prdctCode
                    , round(SUM(t.shares), 2) AS buy_shares
                FROM trdRcd t
                WHERE t.accntId = {accntId}
                    AND t.type = 1
                GROUP BY accntId, prdctCode
            ) a
                LEFT JOIN (
                    -- 加总赎回份额
                    SELECT accntId, prdctCode
                        , round(SUM(t.shares), 2) AS sell_shares
                    FROM trdRcd t
                    WHERE t.accntId = {accntId}
                        AND t.type = 2
                    GROUP BY accntId, prdctCode
                ) b
                ON a.accntId = b.accntId
                    AND a.prdctCode = b.prdctCode
        ) t2    
    """.format(accntId=accnt_id)
    return sql


def get_accnt_asst2(accnt_id):
    """
    获得一个账户下的持仓产品及剩余份额，以卖出记录左关联买入记录
    :return: 账户ID，账户持有产品代码，产品剩余份额
    """
    sql = """
        SELECT t2.accntId, t2.prdctCode
            , round(t2.sell_shares - t2.buy_shares, 2) AS left_shares
        FROM (
            SELECT a.*, IFNULL(b.buy_shares, 0) AS buy_shares
            FROM (
                -- 加总购买份额
                SELECT accntId, prdctCode
                    , round(SUM(t.shares), 2) AS sell_shares
                FROM trdRcd t
                WHERE t.accntId = {accntId}
                    AND t.type = 2
                GROUP BY accntId, prdctCode
            ) a
                LEFT JOIN (
                    -- 加总赎回份额
                    SELECT accntId, prdctCode
                        , round(SUM(t.shares), 2) AS buy_shares
                    FROM trdRcd t
                    WHERE t.accntId = {accntId}
                        AND t.type = 1
                    GROUP BY accntId, prdctCode
                ) b
                ON a.accntId = b.accntId
                    AND a.prdctCode = b.prdctCode
        ) t2
    """.format(accntId=accnt_id)
    return sql


def get_accnt_prtfl_cfg(accnt_id):
    """
    获得一个账户下的持仓产品对应的比例配置信息
    :return: 账户ID，产品代码，剩余份额，产品名称，目标比例，规则状态
    """
    sql = """
        SELECT t3.*, s.prdctName, s.trgtPrcnt, s.asctPrdcts
            , ifnull(s.status, 0) AS ruleStatus
        FROM (
            SELECT t2.accntId, t2.prdctCode
                , round(t2.buy_shares - t2.sell_shares, 2) AS leftShares
            FROM (
                SELECT a.*, IFNULL(b.sell_shares, 0) AS sell_shares
                FROM (
                    -- 加总购买份额
                    SELECT accntId, prdctCode
                        , round(SUM(t.shares), 2) AS buy_shares
                    FROM trdRcd t
                    WHERE t.accntId = {accntId}
                        AND t.type = 1
                    GROUP BY accntId, prdctCode
                ) a
                    LEFT JOIN (
                        -- 加总赎回份额
                        SELECT accntId, prdctCode
                            , round(SUM(t.shares), 2) AS sell_shares
                        FROM trdRcd t
                        WHERE t.accntId = {accntId}
                            AND t.type = 2
                        GROUP BY accntId, prdctCode
                    ) b
                    ON a.accntId = b.accntId
                        AND a.prdctCode = b.prdctCode
            ) t2
            WHERE round(t2.buy_shares - t2.sell_shares, 2) > 0
        ) t3
            LEFT JOIN prcntRule s
            ON t3.accntId = s.accntId
                AND t3.prdctCode = s.prdctCode    
    """.format(accntId=accnt_id)
    return sql


def get_accnt_grid_cfg(accnt_id):
    """
    获得一个账户下的持仓产品对应的比例配置信息
    :return: 账户ID，产品代码，剩余份额，产品名称，目标比例，规则状态
    """
    sql = f"""
        SELECT t3.*, t1.accntName, t1.gridFlag
        FROM accnts t1
            LEFT JOIN (
                SELECT t2.*, s.prdctName, s.gridRisePrcnt, s.riseSellTrgt, s.gridDownPrcnt
                    , s.downBuyTrgt, ifnull(s.status, 2) AS ruleStatus
                FROM (
                    SELECT t.accntId, t.prdctCode
                        , round(t.buy_shares - t.sell_shares, 2) AS leftShares
                    FROM (
                        SELECT a.*, IFNULL(b.sell_shares, 0) AS sell_shares
                        FROM (
                            -- 加总购买份额
                            SELECT accntId, prdctCode
                                , round(SUM(t.shares), 2) AS buy_shares
                            FROM trdRcd t
                            WHERE t.accntId = {accnt_id}
                                AND t.type = 1
                            GROUP BY accntId, prdctCode
                        ) a
                            LEFT JOIN (
                                -- 加总赎回份额
                                SELECT accntId, prdctCode
                                    , round(SUM(t.shares), 2) AS sell_shares
                                FROM trdRcd t
                                WHERE t.accntId = {accnt_id}
                                    AND t.type = 2
                                GROUP BY accntId, prdctCode
                            ) b
                            ON a.accntId = b.accntId
                                AND a.prdctCode = b.prdctCode
                    ) t
                    WHERE round(t.buy_shares - t.sell_shares, 2) > 0
                ) t2
                    LEFT JOIN gridRule s
                    ON t2.accntId = s.accntId
                        AND t2.prdctCode = s.prdctCode
            ) t3
            ON t1.accntId = t3.accntId
        WHERE t1.status = 'act'
            AND t1.gridFlag = '1'
    """
    return sql


def get_accnt_prdct_rule(accnt_user_id, accnt_name, prdct_name):
    sql = f"""
        SELECT s.prdctCode, s.prdctName
        FROM accnts t, prcntRule s
        WHERE t.accntUserId = \'{accnt_user_id}\'
            And t.accntName = \'{accnt_name}\'
            AND t.accntId = s.accntId
            AND s.`status` = 1
            AND s.prdctName = \'{prdct_name}\'    
    """
    return sql


def get_accnt_id_by_user(accnt_user_id, accnt_name):
    sql = f"""
        SELECT t.accntId, t.accntUserId, t.accntName
        FROM accnts t
        WHERE t.accntUserId = \'{accnt_user_id}\'
            AND t.accntName = \'{accnt_name}\'
    """
    return sql


def clr_trd_rcd(accnt_id, prdct_code, trd_date, srl_num):
    sql = f"""
        DELETE FROM trdRcd
        WHERE accntId = {accnt_id}
            AND prdctCode = \'{prdct_code}\'
            AND trdDate = \'{trd_date}\'
            AND type = 1
            AND srlNum = {srl_num}    
    """
    return sql


def set_trd_rcd(accnt_id, prdct_code, trd_date, type, srl_num, cnfrm_date, amnt, nv, shares, remark):
    sql = f"""
        INSERT INTO trdRcd (accntId, prdctCode, trdDate, type, srlNum, cnfrmDate, amount, nv, shares, remark)
        VALUES ({accnt_id}, \'{prdct_code}\', \'{trd_date}\', {type}, {srl_num}, \'{cnfrm_date}\', {amnt}, {nv}, {shares}, \'{remark}\')
    """
    return sql
